﻿using Dapper;
using System;
using BCEntity.Trading.SaleGoods.SaleGoodsNorm;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.Trading.SaleGoods.SaleGoods;

namespace BCData.Trading.SaleGoods.SaleGoodsNorm
{
    /// <summary>
    /// 销售商品规格
    /// </summary>
    public class SaleGoodsNormData : ISaleGoodsNormData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM salegoodsnorm";
        private const string QUERY = @"SELECT SaleGoodsNormId,NormIndex,MainImage,HasBargain,HasStepPrice,SaleAmount,StepOneStartNumber,StepOneEndNumber,StepTwoStartNumber,
                                            StepTwoEndNumber,StepThreeStartNumber,SaleGoodsCategoryId,StepThreeEndNumber,StepOnePrice,StepTwoPrice,StepThreePrice,SaleUnit,
                                            HasContentHeaderVideo,ContentHeaderVideo,HasContentSummary,ContentSummary,Content,CategoryName,HasRelatedProducts,
                                            RelatedProductsIdPath,InventoryCount,SaleCount,Enabled,CreateAdminId,CreateAdminName,CreateTime,UpdateTime,EditAdminId,
                                            SaleGoodsId,EditAdminName,GoodsName,SKU,NormName,NormNamePinyin,NormNo 
                                        FROM salegoodsnorm";
        public SaleGoodsNormData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #region 检查销售商品规格是否存在
        public async Task<bool> Exists(long saleGoodsNormId)
        {
            string sql = string.Format(@"{0} WHERE SaleGoodsNormId=?SaleGoodsNormId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsNormId", saleGoodsNormId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> Exists(string normNo)
        {
            string sql = string.Format(@"{0} WHERE NormNo=?NormNo", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NormNo", normNo, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 添加销售商品规格
        public async Task<SaleGoodsNormEntity> Add(SaleGoodsNormEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("salegoodsnorm", new string[] { "SaleGoodsCategoryId","CategoryName","SaleGoodsId", "GoodsName", "SKU",
                "NormName", "NormNamePinyin", "NormNo","NormIndex","MainImage", "HasBargain", "HasStepPrice","SaleAmount", "SaleUnit", "StepOneStartNumber", "StepOneEndNumber",
                "StepTwoStartNumber", "StepTwoEndNumber", "StepThreeStartNumber", "StepThreeEndNumber", "StepOnePrice", "StepTwoPrice", "StepThreePrice", "HasContentHeaderVideo",
                "ContentHeaderVideo", "HasContentSummary", "ContentSummary", "Content","HasRelatedProducts", "RelatedProductsIdPath", "InventoryCount","CreateAdminId",
                "CreateAdminName"  });
            sql = string.Format("{0} {1} WHERE SaleGoodsNormId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsCategoryId", entity.SaleGoodsCategoryId, DbType.Int64, size: 20);
            parameters.Add("?CategoryName", entity.CategoryName?.Trim(), DbType.AnsiString, size: 10);
            parameters.Add("?SaleGoodsId", entity.SaleGoodsId, DbType.Int64, size: 20);
            parameters.Add("?GoodsName", entity.GoodsName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?SKU", entity.Sku?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?NormName", entity.NormName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?NormNamePinyin", entity.NormNamePinyin?.Trim(), DbType.AnsiString, size: 800);
            parameters.Add("?NormNo", entity.NormNo?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?NormIndex", entity.NormIndex, DbType.Int32, size: 11);
            parameters.Add("?MainImage", entity.MainImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?HasBargain", entity.HasBargain, DbType.Boolean);
            parameters.Add("?HasStepPrice", entity.HasStepPrice, DbType.Boolean);
            parameters.Add("?SaleAmount", entity.SaleAmount, DbType.Decimal, size: 16);
            parameters.Add("?SaleUnit", entity.SaleUnit?.Trim(), DbType.AnsiString, size: 10);
            parameters.Add("?StepOneStartNumber", entity.StepOneStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepOneEndNumber", entity.StepOneEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepTwoStartNumber", entity.StepTwoStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepTwoEndNumber", entity.StepTwoEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepThreeStartNumber", entity.StepThreeStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepThreeEndNumber", entity.StepThreeEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepOnePrice", entity.StepOnePrice, DbType.Decimal, size: 16);
            parameters.Add("?StepTwoPrice", entity.StepTwoPrice, DbType.Decimal, size: 16);
            parameters.Add("?StepThreePrice", entity.StepThreePrice, DbType.Decimal, size: 16);
            parameters.Add("?HasContentHeaderVideo", entity.HasContentHeaderVideo, DbType.Boolean);
            parameters.Add("?ContentHeaderVideo", entity.ContentHeaderVideo?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?HasContentSummary", entity.HasContentSummary, DbType.Boolean);
            parameters.Add("?ContentSummary", entity.ContentSummary?.Trim(), DbType.String);
            parameters.Add("?Content", entity.Content, DbType.String);
            parameters.Add("?HasRelatedProducts", entity.HasRelatedProducts, DbType.Boolean);
            parameters.Add("?RelatedProductsIdPath", entity.RelatedProductsIdPath?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?InventoryCount", entity.InventoryCount, DbType.Int32, size: 11);
            parameters.Add("?CreateAdminId", entity.CreateAdminId, DbType.Int64, size: 20);
            parameters.Add("?CreateAdminName", entity.CreateAdminName?.Trim(), DbType.AnsiString, size: 20);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleGoodsNormEntity>(sql, parameters, databaseContext.Transaction);

        }
        #endregion

        #region 修改销售商品规格
        public async Task<SaleGoodsNormEntity> Update(SaleGoodsNormEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("salegoodsnorm", new string[] { "NormIndex", "MainImage", "HasBargain", "HasStepPrice", "SaleAmount", "StepOneStartNumber",
                "StepOneEndNumber", "StepTwoStartNumber", "StepTwoEndNumber", "StepThreeStartNumber", "SaleGoodsCategoryId", "StepThreeEndNumber", "StepOnePrice",
                "StepTwoPrice", "StepThreePrice", "SaleUnit", "HasContentHeaderVideo", "ContentHeaderVideo", "HasContentSummary", "ContentSummary", "Content",
                "CategoryName", "HasRelatedProducts", "RelatedProductsIdPath", "InventoryCount", "EditAdminId", "SaleGoodsId", "EditAdminName", "GoodsName",
                "SKU", "NormName", "NormNamePinyin", "NormNo" }, new string[] { "SaleGoodsNormId" });
            sql = string.Format("{0} {1} WHERE SaleGoodsNormId=?SaleGoodsNormId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsNormId", entity.SaleGoodsNormId, DbType.Int64, size: 20);
            parameters.Add("?NormIndex", entity.NormIndex, DbType.Int32, size: 11);
            parameters.Add("?MainImage", entity.MainImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?HasBargain", entity.HasBargain, DbType.Boolean);
            parameters.Add("?HasStepPrice", entity.HasStepPrice, DbType.Boolean);
            parameters.Add("?SaleAmount", entity.SaleAmount, DbType.Decimal, size: 16);
            parameters.Add("?StepOneStartNumber", entity.StepOneStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepOneEndNumber", entity.StepOneEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepTwoStartNumber", entity.StepTwoStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepTwoEndNumber", entity.StepTwoEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepThreeStartNumber", entity.StepThreeStartNumber, DbType.Int32, size: 11);
            parameters.Add("?SaleGoodsCategoryId", entity.SaleGoodsCategoryId, DbType.Int64, size: 20);
            parameters.Add("?StepThreeEndNumber", entity.StepThreeEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepOnePrice", entity.StepOnePrice, DbType.Decimal, size: 16);
            parameters.Add("?StepTwoPrice", entity.StepTwoPrice, DbType.Decimal, size: 16);
            parameters.Add("?StepThreePrice", entity.StepThreePrice, DbType.Decimal, size: 16);
            parameters.Add("?SaleUnit", entity.SaleUnit?.Trim(), DbType.AnsiString, size: 10);
            parameters.Add("?HasContentHeaderVideo", entity.HasContentHeaderVideo, DbType.Boolean);
            parameters.Add("?ContentHeaderVideo", entity.ContentHeaderVideo?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?HasContentSummary", entity.HasContentSummary, DbType.Boolean);
            parameters.Add("?ContentSummary", entity.ContentSummary?.Trim(), DbType.String);
            parameters.Add("?Content", entity.Content, DbType.String);
            parameters.Add("?CategoryName", entity.CategoryName?.Trim(), DbType.AnsiString, size: 10);
            parameters.Add("?HasRelatedProducts", entity.HasRelatedProducts, DbType.Boolean);
            parameters.Add("?RelatedProductsIdPath", entity.RelatedProductsIdPath?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?InventoryCount", entity.InventoryCount, DbType.Int32, size: 11);
            parameters.Add("?EditAdminId", entity.EditAdminId, DbType.Int64, size: 20);
            parameters.Add("?SaleGoodsId", entity.SaleGoodsId, DbType.Int64, size: 20);
            parameters.Add("?EditAdminName", entity.EditAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?GoodsName", entity.GoodsName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?SKU", entity.Sku?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?NormName", entity.NormName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?NormNamePinyin", entity.NormNamePinyin?.Trim(), DbType.AnsiString, size: 800);
            parameters.Add("?NormNo", entity.NormNo?.Trim(), DbType.AnsiString, size: 100);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleGoodsNormEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改销售商品信息
        public async Task<bool> UpdateGoodsInfo(SaleGoodsEntity entity)
        {
            string sql = @"UPDATE salegoodsnorm SET 
                                SaleGoodsCategoryId=?SaleGoodsCategoryId,
                                CategoryName=?CategoryName,
                                GoodsName=?GoodsName,
                                SKU=?SKU
                            WHERE SaleGoodsId=?SaleGoodsId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsId", entity.SaleGoodsId, DbType.Int64);
            parameters.Add("?GoodsName", entity.GoodsName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?SKU", entity.Sku?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?SaleGoodsCategoryId", entity.SaleGoodsCategoryId, DbType.Int64, size: 20);
            parameters.Add("?CategoryName", entity.CategoryName?.Trim(), DbType.AnsiString, size: 10);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 删除销售商品规格
        public async Task<bool> Delete(long saleGoodsNormId)
        {
            string sql = @"DELETE FROM salegoodsnorm WHERE SaleGoodsNormId=?SaleGoodsNormId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsNormId", saleGoodsNormId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 获取销售商品规格
        public async Task<SaleGoodsNormEntity> GetEntity(long saleGoodsNormId)
        {
            string sql = string.Format(@"{0} WHERE SaleGoodsNormId=?SaleGoodsNormId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsNormId", saleGoodsNormId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<SaleGoodsNormEntity>(sql, parameters);
        }
        #endregion

        #region 获取销售商品规格列表
        public async Task<IEnumerable<SaleGoodsNormEntity>> GetList()
        {
            string sql = string.Format(@"{0} WHERE Enabled=1", QUERY);
            return await this.databaseContext.Slave.QueryAsync<SaleGoodsNormEntity>(sql);
        }
        #endregion

        #region 获取销售商品规格列表
        public async Task<IEnumerable<SaleGoodsNormEntity>> GetList(long saleGoodsId)
        {
            string sql = string.Format(@"{0} WHERE SaleGoodsId=?SaleGoodsId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsId", saleGoodsId, DbType.Int64);
            return await this.databaseContext.Slave.QueryAsync<SaleGoodsNormEntity>(sql, parameters);
        }
        #endregion

        #region 获取销售商品规格分页
        public async Task<(IEnumerable<SaleGoodsNormEntity>, int)> Query(string categoryName, string goodsName, string sku, bool? enabled, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
           
            if (!string.IsNullOrWhiteSpace(categoryName))
            {
                wheres.Add("CategoryName LIKE CONCAT('%',CONCAT(?CategoryName,'%'))");
                parameters.Add("?CategoryName", categoryName, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(goodsName))
            {
                wheres.Add("GoodsName LIKE CONCAT('%',CONCAT(?GoodsName,'%'))");
                parameters.Add("?GoodsName", goodsName, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                wheres.Add("SKU LIKE CONCAT('%',CONCAT(?SKU,'%'))");
                parameters.Add("?SKU", sku, DbType.AnsiString);
            }
            if (enabled.HasValue)
            {
                wheres.Add("Enabled=?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM salegoodsnorm {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<SaleGoodsNormEntity, int>(sql, parameters);
        }
        #endregion

        #region 启用销售商品规格
        public async Task<bool> Enabled(long saleGoodsNormId)
        {
            string sql = @"UPDATE salegoodsnorm SET Enabled=1 WHERE SaleGoodsNormId=?SaleGoodsNormId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsNormId", saleGoodsNormId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 禁用销售商品规格
        public async Task<bool> Disabled(long saleGoodsNormId)
        {
            string sql = @"UPDATE salegoodsnorm SET Enabled=0 WHERE SaleGoodsNormId=?SaleGoodsNormId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsNormId", saleGoodsNormId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion
    }
}



